# coding=utf-8
"本模块定义常用函数，并可用于其他Python Excel项目"
from datetime import datetime
from typing import Union
import xlwings as xw


def read1stSheet(filePath: str) -> tuple[xw.Sheet, list[list], xw.Book]:
    """读取某文件第1个sheet的内容

    Args:
        filePath (str): 该文件的路径

    Returns:
        tuple: (第一个sheet, 第一个sheet的data, 该workbook)
    """
    wb1 = xw.Book(filePath)  # 获取其中的workbook
    # app.close()
    return (wb1.sheets[0], wb1.sheets[0].used_range.value, wb1)


def doesSheetExist(sheetName: str, wb: xw.Book, add: bool = False) -> bool:
    """某个workbook是否存在名为sheetName的sheet

    Args:
        sheetName (str): 要查询的sheet的Name
        wb (object, optional): 该workbook. Defaults to wb0.
        add (bool, optional): 如果未存在，是否要添加. Defaults to False.

    Returns:
        bool: [description]
    """
    for sheet in wb.sheets:  # 遍历wb0的所有sheet
        if sheet.name == sheetName:  # 如果该sheet的名称与此相符
            return True
    else:
        if add:
            wb.sheets.add(sheetName)
        return False


def deleteSheetIfExists(sheetName: str, wb: xw.Book) -> None:
    """如果在wb中存在名为sheetName的sheet就删除

    Args:
        sheetName (str): [description]
        wb ([type], optional): [description]. Defaults to wb0.
    """
    if doesSheetExist(sheetName, wb):
        wb.sheets[sheetName].delete()


def createSheetIfNotExist(sheetName: str, wb: xw.Book, setBlack: bool = False) -> xw.Sheet:
    """某个workbook是否存在名为sheetName的sheet

    Args:
        sheetName (str): 要查询的sheet的Name
        wb (object, optional): 该workbook. Defaults to wb0.
        setBlack (bool, optional): 设为黑色. Defaults to False.

    Returns:
        object: sheet对象
    """
    def handleSetBlack():
        if setBlack:
            # 该sheet的背景色改为黑色
            wb.sheets[sheetName].api.Tab.ThemeColor = 2
            wb.sheets[sheetName].api.Tab.TintAndShade = 0
    for sheet in wb.sheets:  # 遍历wb0的所有sheet
        if sheet.name == sheetName:  # 如果该sheet的名称与此相符
            handleSetBlack()
            return wb.sheets[sheetName]
    else:
        wb.sheets.add(sheetName)
        handleSetBlack()
        return wb.sheets[sheetName]


def importSheet(sheetFrom: xw.Sheet, newSheetName: str, colNames: set, wb: xw.Book, byRange: bool = True, setBlack: bool = True) -> bool:
    """将某个sheet导入到wb0

    Args:
        sheetFrom (object): 要导入的sheet
        newSheetName (str): 导入以后的Sheet名
        colNames (set): 验证列名用的合集，如果列名都包含在内，返回True
        wb (object, optional): 导入到哪个workbook里，默认是wb0
        byRange (bool, optional): 将所有的range复制过来而不是整个sheet. Defaults to True.

    Returns:
        bool: 是否验证成功并导入
    """
    sheetData = sheetFrom.used_range.value
    if isinstance(sheetData, list) and isinstance(sheetData[0], list):
        colNames1 = set(sheetData[0])  # 要导入的表格的第一行内容
        if len(colNames & colNames1) == len(colNames):  # 如果标题交集的长度 = 用于检查的长度
            if byRange:
                sheetTo = createSheetIfNotExist(
                    newSheetName, wb, setBlack=True)
                sheetTo.used_range.clear()
                sheetFrom.used_range.copy(sheetTo["A1"])
            else:
                if doesSheetExist(newSheetName, wb):  # 如果要导入的sheet名已存在
                    wb.sheets[newSheetName].delete()  # 直接将该sheet删除
                sheetFrom.copy(after=wb.sheets[-1], name=newSheetName)
                if setBlack:
                    # 该sheet的背景色改为黑色
                    wb.sheets[newSheetName].api.Tab.ThemeColor = 2
                    wb.sheets[newSheetName].api.Tab.TintAndShade = 0
                # 添加时间戳
            timeStr = datetime.now()
            wb.sheets[newSheetName]["A1"].api.AddComment(
                f"本Sheet导入日期\n{timeStr.year}-{timeStr.month}-{timeStr.day}"
            )
            return True

        else:
            return False
    return False


def sheetToDictList(sheet: xw.Sheet, startRow: int = 1, titleRow: int = 0) -> list[dict]:
    """将sheet的used_range转换为list

    Args:
        sheet (object): 要导入的list
        startRow (int, optional): 从第几行开始(以0为起点). Defaults to 1.
        titleRow (int, optional): 列标题所在行(以0为起点). Defaults to 0.

    Returns:
        list: 每个元素为一个dict, 键为列标题
    """
    sheetData = sheet.used_range.value  # 获取该sheet的数据
    if isinstance(sheetData, list):
        titles = sheetData[titleRow]  # 获取该data的title

        def rowToDict(row):  # 格式转换函数
            _dict = {}  # 创建一个空字典
            for i, val in enumerate(row):
                _dict[titles[i]] = val
            return _dict
        return list(map(rowToDict, sheetData[startRow:]))
    else:
        return []


def sheetToDict(sheet: xw.Sheet, startRow: int = 1, titleRow: int = 0, key: str = "人员代码") -> dict:
    """将sheet的used_range转换为list

    Args:
        sheet (object): 要导入的list
        startRow (int, optional): 从第几行开始(以0为起点). Defaults to 1.
        titleRow (int, optional): 列标题所在行(以0为起点). Defaults to 0.
        key (str, optional): [description]. Defaults to "人员代码".
        formatKey (def,None) 将key转换以下的函数

    Returns:
        dict: 以key为键创建的字典
    """
    data = sheetToDictList(sheet, startRow, titleRow)
    obj = {}
    for val in data:
        obj[val[key]] = val
    return obj


def setAllLines(range: xw.Range, weight: int = 2):
    """为range画上所有线

    Args:
        range (object): 要划线的range
    """
    range.api.HorizontalAlignment = -4108
    range.api.VerticalAlignment = -4130
    for i in [7, 8, 9, 10, 11, 12]:
        range.api.Borders(i).LineStyle = 1
        range.api.Borders(i).Weight = weight


def forceToStr(data: Union[str, dict, list]):
    """转换为强制显示该文字的公式

    Args:
        data (str or dict or list): 要导入的内容
    """
    def handleLine(line):  # 对每一行(line)进行修正
        if not isinstance(line, (list, dict)):
            return f'="{line}"'  # 如果不是字符串，而是数字等
        elif isinstance(line, dict):
            for key in line.keys():  # 如果这一行是字典类型
                line[key] = f'="{line}"'
        else:
            for key in range(len(line)):  # 如果这一行是数组类型
                line[key] = f'="{line}"'
    if isinstance(data, str):
        return f'="{data}"'
    elif isinstance(data, dict):
        for key in data.keys():
            data[key] = handleLine(data[key])
    elif isinstance(data, list):
        for key in range(len(data)):
            data[key] = handleLine(data[key])
    return data


# def toStr(colIndex: int) -> str:
#     num = colIndex
#     accurateLevel = math.log(colIndex, 26)
#     howMany = math.floor(accurateLevel)
#     result = ""
#     for i in range(howMany, 0, -1):
#         level = pow(26, i)
#         someInt = round(num / level)
#         num = num % round(level)
#         charIndex = someInt + 64
#         char = chr(charIndex)
#         print(charIndex)
#         result += char
#     return result
